﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_SHOP_Get_Shop_Location')
	BEGIN
		DROP Procedure usp_UPDMS_REF_SHOP_Get_Shop_Location
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_SHOP_Get_Shop_Location
**	Desc : 업소 위치정보
**	Test Exec Query : Exec usp_UPDMS_REF_SHOP_Get_Shop_Location 'SP001','Y'
**	Called by : Ref_Dac_UPDMS_REF_SHOP.cs
**	Program ID : NaverMapMulitMarker, Ref1012m
**	Auth : 송시명
**	Date : 2013-05-12
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_SHOP_Get_Shop_Location]
@ls_shop_id nvarchar(5),
@ls_favorite_yn nvarchar(1)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

--업소
SELECT Latitude,
       Longitude,
       Latitude + ',' + Longitude + ',' + Shop_Nm AS Markers
  FROM UPDMS_REF_SHOP WITH(NOLOCK)
 WHERE LEN(Latitude + Longitude) = 12
   AND Shop_Id LIKE @ls_shop_id + '%'
   AND Favorite_Yn LIKE @ls_favorite_yn + '%'


--기행기
SELECT DISTINCT urt.Latitude,
       urt.Longitude,
       urt.Latitude + ',' + urt.Longitude + ',' + urs.Shop_Nm AS Markers
  FROM UPDMS_REF_TOUR urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE LEN(urt.Latitude + urt.Longitude) = 12
   AND urt.Shop_Id LIKE @ls_shop_id + '%'

GO